ContactsLaw leverages the open-source mathematical expression library NCalc to provide support for simple formulas. These can be used in dynamic descriptions on documents/files and in formatting rules for document templates, as well as in the Interactive Forms Framework.
Syntax
Formulae in ContactsLaw use a familiar mathematical syntax, as found in calculators, spreadsheets and macros in other software packages. You can use any of the following constructs:
| Literals* |
1
2
3
'example text' |
| Operators (+ - * /) |
1 + 2 + 3
2 * 3 |
| Parentheses |
(2 * 3) + 20 |
| Variables |
x + 7
(2 * x) + 1 |
* - Note: Strings containing apostrophes or backslash characters must be escaped by adding another backslash in front of them; e.g. 'John\'s cat'. Conditional expressions
When you perform a test or comparison on a value (usually in relation to the 'If' function), the result will be 'True' or 'False', depending on the outcome.
You can use the following conditional operators in your formulae:
| = |
Equal to |
x = 5 |
!=
<> |
Not equal to |
y != 3
y <> 3 |
| < |
Less than |
x < 10 |
| <= |
Less than or equal to |
y <= 30 |
| > |
Greater than |
x > 2 |
| >= |
Greater than or equal to |
y >= 4 |
| not |
Not (negate) |
not x
not (x = 5) |
| and |
And (both must be true) |
(x > 1) and (x < 10) |
| or |
Or (at least one must be true) |
(x < 10) or (x > 20) |
Parentheses and the boolean operators (and/or/not) allow you to combine several tests/comparisons into a single expression.
Functions
You can also call functions in formulae. ContactsLaw provides an extensive set of functions on top of NCalc's built-in functions that make it easier to work with dates, text, sets of values and tabular data.
The following functions are supported:
Mathematical functions (built-in)
Abs Cos, Cosh, Acos Sin, Sinh, Asin Tan, Tanh, Atan, Atan2 Ceiling, Floor Pow, Exp Log, Log10 Min, Max Round, Truncate Sqrt
Date and time functions
These functions operate on values which represents dates and times. They must be expressed in a suitable format, preferably yyyy/MM/dd.
In the examples below, assume the variable 'x' contains the date '2016/07/01 12:00pm'.
| AddDays(date, days) |
Adds the specified number of days onto a date value. |
AddDays(x, 5) => '2016/07/06'
AddDays(x, -10) => '2016/06/21' |
| AddMonths(date, months) |
Adds the specified number of months onto a date value. |
AddMonths(x, 1) => '2016/08/01' |
| AddYears(date, years) |
Adds the specified number of years onto a date value. |
AddYears(x, -2) => '2014/07/01' |
| AddBusinessDays(date, days) |
Adds the specified number of business days (i.e. skipping weekends) onto a date value. |
AddBusinessDays(x, 7) => '2016/07/12' |
| AddHours(date, hours) |
Adds the specified number of hours onto a date value. |
AddHours(x, 2) => '2:00pm' |
| AddMinutes(date, minutes) |
Adds the specified number of minutes onto a date value. |
AddMinutes(x, -30) => '11:30am' |
| SetTime(date, [hours], [minutes]) |
Sets the time component of a date value. Omit the second and third parameters to remove the time component. |
SetTime(x, 14, 30) => '2:30pm'
SetTime(x) => '12:00am' |
| SetTime(date, [timespan]) |
Sets the time component of a date value. This version of the function lets you express the value as a timespan, e.g. '15:30'. |
SetTime(x,'10:45') => '10:45am' |
| Day(date) |
Gets the day component of a date value. |
Day(x) => '1' |
| Month(date) |
Gets the month component of a date value. |
Month(x) => '7' |
| Year(date) |
Gets the year component of a date value. |
Year(x) => '2016' |
| Hour(date) |
Gets the hour component of a date value (0-23). |
Hour(x) => '12' |
| Minute(date) |
Gets the minute component of a date value (0-59). |
Minute(x) => '0' |
| Today() |
Gets today's date. |
Today() => '2016/12/20' |
| Now() |
Gets today's date, including the current time. |
Now() => '2016/12/20 2:45pm' |
| Days(date1, date2) |
Gets the number of days between two date values. |
Days(x, '2016/07/10') => '9'
Days(x, AddDays(x, 1)) => '1' |
| Minutes(date1, date2) |
Gets the number of minutes between two date values. |
Minutes(x, '2016/07/01 12:15pm') => '15'
Minutes(x, AddHours(x,1)) => '60' |
| Years(date1, date2) |
Gets the number of years between two date values, rounded down. |
Years(x, '2017/10/01') => '1'
Years(x, AddYears(x,2)) => '2' |
| Age(date) |
Gets the number of years that have elapsed since a particular date, rounded down. |
Age(x) => '0' |
Date(value, [format])
Date(set, [format]) |
Converts any value into a date and formats it in a particular way. The second parameter is optional. The alternate form applies date formatting to a set of values. |
Date(x) => '01/07/2016'
Date(x, 'd MMM yyyy') => '1 Jul 2016' |
Text and number functions
These functions operate on values which represent text or numbers. Some functions accept multiple arguments; most of these also accept sets (see below).
In the examples below, the variable 'x' contains the value 'sample text', 'y' contains '150.28' and 'z' contains 'word'.
Number(value, [format])
Number(set, [format]) |
Converts any value into a number and formats it in a particular way. The second parameter is optional. The alternate form applies number formatting to a set of values. |
Number(y) => '150.28'
Number(y, 'N1') => '150.3' |
| Text(value) |
Converts any value into text. This enables other types of values to be treated as text. |
Text(x) => 'sample text'
Text(100) => '100' |
| Replace(value, find, replace) |
Searches in 'value' and replaces all occurrences of 'find' with 'replace'. (Advanced users note: 'find' can be a regular expression) |
Replace('cat basket', 'cat', 'dog') => 'dog basket'
Replace('hello ?', '?', x) => 'hello sample text' |
| Concat(value1, value2, ... valueN) |
Concatenates (joins) two or more text values together. |
Concat(x,y) => 'sample text150.28'
Concat(x,' and ',y) => 'sample text and 150.28' |
| Coalesce(value1, value2, ... valueN) |
Returns the first non-empty value (reading the parameters from left to right). |
Coalesce(x,y) => 'sample text'
Coalesce('','nonempty','ignored') => 'nonempty' |
List(value1, value2, ... valueN)
List(set) |
Displays a set of values, separated by commas, with the last two values joined by the word "and". Empty values are ignored. |
List(x, y, z) => 'sample text, 150.28 and word'
List(Column(x, 1)) |
Join(separator, value1, value2, ... valueN)
Join(sepatator, set) |
Joins a set of values together using a particular separator. Empty values are ignored. |
Join(' ', x, y, z) => 'sample text 150.28 word'
Join(' ', 'cat', '', 'bird') => 'cat bird'
Join('+', Column(x, 2)) |
Prefix(value, text)
Prefix(set, text) |
Inserts 'text' at the start of 'value', or at the start of each item in 'set'. |
Prefix(x, 'Author: ') => 'Author: sample text'
Prefix(Column(x, 1), 'Mr. ') |
Suffix(value, text)
Suffix(set, text) |
Inserts 'text' at the end of 'value', or at the end of each item in 'set'. |
Suffix(x, ' (deceased)') => 'sample text (deceased)'
Suffix(Row(x, 2), ' and others') |
Plural(word)
Plural(set, word) |
Returns the plural of 'word'. (Alternate form: if the set contains two or more values, returns the plural of 'word'. If the set contains only one value, returns 'word'. If the set is empty, returns nothing.) |
Plural('apple') => 'apples'
Plural(Column(x, 1), 'Defendant') |
| Possessive(word) |
Adds a possessive apostrophe to 'word' according to grammar rules. |
Possessive('Boris') => 'Boris''
Possessive('Sally') => 'Sally's' |
| Format(text, value1, value2, ... valueN) |
Constructs a line of text incorporating one or more values (referred to by $n where 'n' is their number in sequence). |
Format('Page $1 of $2', pageNo, pages) |
Functions that operate on sets
Sets are expressions that represent multiple values. You obtain a set by passing a table of values and an offset to the Column/Row function. You cannot construct a set using literals. Sets are mainly used as intermediate values in formulas, and must be collapsed using the List/Join/Count functions to produce meaningful output.
| Column(table, n) |
Returns the set of values contained in the n-th column of 'table'. Empty values are ignored. |
Column(x, 3) |
| Row(table, n) |
Returns the set of values containing in the n-th row of 'table'. Empty values are ignored. |
Row(x, 2) |
| Cell(table, column, row) |
Returns the single value at the intersection of 'column' and 'row' in 'table'. |
Cell(x, 3, 2) |
| Union(set1, set2) |
Returns a new set containing the distinct values in both sets. You can specify more than two sets. |
Union(x, y)
Union(Column(x, 1), Column(x, 2)) |
| Intersection(set1, set2) |
Returns a new set containing the distinct values common to both sets. |
Intersection(x, y)
Intersection(Row(x, 1), Row(x, 2)) |
| Except(set1, set2) |
Returns a new set containing the distinct values in 'set1' which are not present in 'set2'. |
Except(x, y)
Except(Column(x, 1), Column(x, 2)) |
| Count(set) |
Returns the number of values in a set. |
Count(x)
Count(Column(x, 3)) |
| Sum(set) |
Returns the sum of the values in a set. Values are assumed to be decimal numbers. |
Sum(x)
Sum(Column(x,1)) |
| Average(set) |
Returns the average of the values in a set. Values are assumed to be decimal numbers. |
Average(x)
Average(Column(x,1)) |
| Minimum(set) |
Returns the smallest value in a set or, if the set contains dates, the earliest date. |
Minimum(x)
Minimum(Column(x,1)) |
| Maximum(set) |
Returns the largest value in a set or, if the set contains dates, the latest date. |
Maximum(x)
Maximum(Column(x,1)) |
| First(set) |
Returns the first value in a set. |
First(x)
First(Column(x,1)) |
| Single(set) |
Returns the only value in a set, or empty if there are multiple values. |
Single(x)
Single(Column(x,1)) |
| Table(set1, set2, ... setN) |
Creates a table from one or more sets. Each set represents a column of values. |
Table(Column(x, 1), Column(x, 3)) |
| ColumnsToRows(table, from, to, n) |
Turns columns into rows by stacking them on top of each other, starting at column number 'from' and ending at column number 'to', in groups of 'n' columns at a time. The last two parameters are optional. |
ColumnsToRows(x, 1, 5)
ColumnsToRows(x, 2, 10, 4) |
| FormatColumns(table, text, column1, column2, ... columnN) |
Constructs a new set of values by combining lines of text constructed from columns in a table. Similar to the Format() function in the previous section. |
FormatColumns(x, '$1 (aged $2)', 1, 2) |
| Query(table, expression, column1, column2, ... columnN) |
Creates a new table containing only those rows which satisfy a particular condition. Optionally, specify the numbers of one or more columns which will appear in the output. To refer to a column value in 'expression', prefix the column number with a dollar sign (e.g. $2 for column 2). |
Query(x, '$2<100')
Query(x, 'Empty($5)', 1, 2, 4) |
| Sort(table, column, direction) |
Creates a new table with the same rows as an existing table, but sorted by a particular column. Optionally specify 'ascending' or 'descending' in the third parameter to reverse the direction of the rows. |
Sort(x, 2)
Sort(x, 3, 'descending') |
Conditional functions
These functions are used in conjunction with conditional expressions (see next section).
In the following examples, the variable 'x' contains the value '2' and 'y' contains '5'.
| If(test, thenValue, [elseValue]) |
Evaluates a conditional expression ('test'). If the result is true, 'thenValue' is returned. If the result is false, 'elseValue' is returned if it is specified (otherwise the result is blank). The value of 'test' can also be a variable containing 'True', 'Yes' or 1 (for true) or 'False', 'No' or 0 (for false). |
If(x>5, 'big', 'small') => 'small'
If(y=2, x) => '' |
Case(testValue,
When(value1, result1),
When(value2, result2),
...
When(valueN, resultN),
[elseValue]) |
Examines the first value ('testValue'). Working from left-to-right, if the value is matched in any of the 'When()' functions, the corresponding result is used. If the value is not matched, the final (optional) value is used. |
Case(x, When(1, 'one'), When(2, 'two'), When(3, 'three'), 'other') => 'two' |
| Empty(value) |
Produces 'True' if the value is empty, or 'False' if it is not empty. |
If(Empty(x), 'value is empty') => '' |
| Contains(set, value) |
Produces 'True' if a particular value is present in a set, otherwise 'False'. Exact matches only. |
If(Contains(Column(x, 1), 'orange'), 'column contains orange') |
IsMatch(value, find)
IsMatch(set, find) |
Searches in 'value' and produces 'True' if one or more occurrences of 'find' are matched. Alternate version returns 'True' if any values in the set are matched by 'find'. (Advanced users note: 'find' can be a regular expression) |
If(IsMatch('one large tree', 'tree'), 'sentence contains tree') => 'sentence contains tree' |
|